﻿using System;
using Gumini.JulaDB.Enums;
using Gumini.JulaDB.Provider;
using Gumini.JulaDB.Test.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Gumini.JulaDB.Test
{
    /// <summary>
    /// Tests covering basic join functionality.
    /// </summary>
    [TestClass]
    public class JoinTest
    {
        private static JulaDbConnection _connection;
        private static int _employeeCount = 0;
        private static int _departmentCount = 0;
        private static readonly int _innerJoinCount = 5;

        [ClassInitialize]
        public static void InitializeDatabase(TestContext context)
        {
            // table definitions and test data are taken form
            // http://en.wikipedia.org/wiki/Join_(SQL)
            _connection = TestUtils.CreateTestDatabase();

            new JulaDbCommand(
              @"create table employee
                (
                    LastName varchar(100),
                    DepartmentId int
                );
                create table department
                (
                    DepartmentId int,
                    DepartmentName varchar(100)
                )", _connection).ExecuteNonQuery();
            
            AddEmployee("Rafferty", 31);
            AddEmployee("Jones", 33);
            AddEmployee("Steinberg", 33);
            AddEmployee("Robinson", 34);
            AddEmployee("Smith", 34);
            AddEmployee("John", null);

            AddDepartment(31, "Sales");
            AddDepartment(33, "Engineering");
            AddDepartment(34, "Clerical");
            AddDepartment(35, "Marketing");
        }

        [ClassCleanup]
        public static void CleanupDatabase()
        {
            _connection.Close();
        }

        private static void AddEmployee(string lastName, int? departmentId)
        {
            new JulaDbCommand(String.Format("insert into employee (LastName, DepartmentId) values ('{0}', {1})",
                lastName, departmentId != null ? departmentId.ToString() : "null"), _connection).ExecuteNonQuery();
            _employeeCount++;
        }

        private static void AddDepartment(int departmentId, string departmentName)
        {
            new JulaDbCommand(String.Format("insert into department (DepartmentId, DepartmentName) values ({0}, '{1}')",
                departmentId, departmentName), _connection).ExecuteNonQuery();
            _departmentCount++;
        }

        /// <summary>
        /// A test for a simple cross join scenatio.
        /// </summary>
        [TestMethod]
        public void SimpleCrossJoin()
        {
            var dataTable = _connection.ExecuteValidSelect(@"select * from employee, department");

            Assert.AreEqual(_departmentCount * _employeeCount, dataTable.Rows.Count);
            Assert.AreEqual(4, dataTable.Columns.Count);
        }

        /// <summary>
        /// An inner join on two tables. Join expression uses table names.
        /// </summary>
        [TestMethod]
        public void TableNameJoin()
        {
            var dataTable = _connection.ExecuteValidSelect(
              @"select *
                from employee, department
                where employee.DepartmentId = department.DepartmentId");

            Assert.AreEqual(_innerJoinCount, dataTable.Rows.Count);
            Assert.AreEqual(4, dataTable.Columns.Count);
        }

        /// <summary>
        /// An inner join on two tables. Join expression uses table aliases.
        /// </summary>
        [TestMethod]
        public void TableAliasJoin()
        {
            var dataTable = _connection.ExecuteValidSelect(
              @"select *
                from employee e, department d
                where e.DepartmentId = d.DepartmentId");

            Assert.AreEqual(_innerJoinCount, dataTable.Rows.Count);
            Assert.AreEqual(4, dataTable.Columns.Count);
        }

        /// <summary>
        /// A reference to one of the columns is ambiguous. This should result with an error.
        /// </summary>
        [TestMethod]
        public void AmbiguousIdentifier()
        {
            _connection.ExecuteInvalidCommand(
              @"select *
                from employee e, department d
                where e.DepartmentId = DepartmentId",
                ErrorCode.AmbiguousIdentifier);
        }
    }
}
